﻿package com.projects.xenia.database_classes;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;


import com.projects.xenia.global_modules.MainGlobalModule;

import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

 public class SqlDataBase {
	
 public void create_tables () {		
	
		
// *** references *** \\
		
		//goods
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_TypeDoc (id TEXT primary key, descr TEXT)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Goods (id TEXT primary key, parentid TEXT,article TEXT,descr_long TEXT,descr_short TEXT,isfolder INTEGER, brands_id TEXT, weight REAL,volume REAL, measure_base TEXT, measure_pack TEXT,minimum_order REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Goods_parentid On xref_Goods (parentid)");

		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_GoodsMeasures (id TEXT primary key, goods_id TEXT, goods_features_id TEXT,measures_id TEXT,mul REAL,tare_goods_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_GoodsMeasures_goods_id On xref_GoodsMeasures (goods_id)");

		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_GoodsDivs (goods_id TEXT primary key, divs TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Measures (id TEXT primary key,descr TEXT )");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_GoodsPrices (id TEXT primary key, goods_id TEXT, pricecategory_id TEXT, goods_features_id TEXT, price REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_GoodsPrices_goods_id_pricecategory_id On xref_GoodsPrices (goods_id,pricecategory_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Brands (id TEXT primary key, descr TEXT)");		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_GoodsBarcodes (barcode TEXT primary key, goods_id TEXT,goods_measures_id TEXT,goods_features_id TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_GoodsRests (goods_id TEXT , goods_features_id TEXT,departments_id TEXT,firms_id TEXT,acctype TEXT,restvalue REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_GoodsRests_goods_id On xref_GoodsRests (goods_id)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Features (id TEXT primary key, descr TEXT)");
		
		// customers & employees
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Customers (id TEXT primary key, descr TEXT, adress TEXT, telephone TEXT,email TEXT,outletdescr TEXT,outletadress TEXT,outlettelephone TEXT, opentime TEXT,closetime TEXT, breacktimefrom TEXT, breacktimeto TEXT,area_id TEXT,searchdescr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Employees (id TEXT primary key, descr TEXT, def TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Routes (id TEXT , employees_id TEXT, position REAL, dayofroute REAL, timeshift REAL,customers_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Routes_employees_id_dayofroute On xref_Routes (employees_id,dayofroute)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Debts (customers_id TEXT,docid TEXT,doctype TEXT,docnum TEXT,docdate TEXT,docpaydate TEXT,totalsum REAL, restsum REAL, acctype TEXT,firms_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Debts_customers_id On xref_Debts (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Debts_Details (docid TEXT , descr TEXT,descr_add TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Debts_Details_docid On xref_Debts_Details (docid)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersAgreements (id TEXT primary key, customers_id TEXT, firms_id TEXT, number TEXT, datebegin TEXT, dateend TEXT)");	
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersAgreements_customers_id On xref_CustomersAgreements (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersLicenses (id TEXT primary key, customers_id TEXT, datebegin TEXT, dateend TEXT,descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersLicenses_customers_id On xref_CustomersLicenses (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersGps (customers_id TEXT primary key, lat TEXT, lon TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersGps_customers_id On xref_CustomersGps (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersCategory (id TEXT primary key, customers_id TEXT , category_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersCategory_customers_id On xref_CustomersCategory (customers_id)");
		
		// addition references
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Areas (id TEXT primary key, descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Category (id TEXT primary key, descr TEXT,comment TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Firms (id TEXT primary key, descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Departments (id TEXT primary key, descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_PriceCategory (id TEXT primary key, descr TEXT)");
		
				
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersDiscounts (customers_id TEXT, goods_id TEXT, discount REAL, pricecategory_id TEXT, is_default INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersDiscounts_customers_id On xref_CustomersDiscounts (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_StockInTrade (id TEXT primary key, descr TEXT, type TEXT )");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersStockInTrade (customers_id TEXT,stockintrade_id TEXT,rest_count REAL, rest_sum REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersStockInTrade_customers_id on xref_CustomersStockInTrade (customers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_PlanOfBrandSales (brands_id TEXT,plan_value REAL,fact_value REAL,day_fact_value REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_PlanOfBrandSales_brands_id on xref_PlanOfBrandSales (brands_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Blanks (id TEXT primary key, descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_BlanksStructure (id TEXT primary key, blank_id TEXT, position INTEGER, field_descr TEXT, field_type, is_required INTEGER )");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_BlanksStructure_blank_id on xref_BlanksStructure (blank_id)");
		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Invoices (id TEXT, number TEXT,date_save TEXT, ismark INTEGER, act INTEGER, total_sum REAL, comment TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Invoices_id on xref_Invoices (id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_OrdersGoodsDetails (id TEXT primary key, descr)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_AssortmentsList (id TEXT primary key, descr TEXT, general INTEGER)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_AssortmentsItems (assortmentslist_id TEXT , goods_id TEXT, factor REAL, target REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_xref_AssortmentsItems_assortmentslist_id_goods_id on xref_AssortmentsItems (assortmentslist_id,goods_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_CustomersAssortments (customers_id TEXT , assortmentslist_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_CustomersAssortments_customers_id on xref_CustomersAssortments (customers_id)");	
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Offers (id TEXT primary key ,descr TEXT,descr_full TEXT,date_begin TEXT, date_end TEXT, offers_condition_type INTEGER,offers_condition_logical_type INTEGER, offers_condition_total_value REAL)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Offers_Conditions (id TEXT primary key, offers_id TEXT, offers_condition_numb INTEGER, offers_condition_sku_count INTEGER,  offers_condition_value REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_Offers_Conditions_offers_id on xref_Offers_Conditions (offers_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Offers_Conditions_Assortments (offers_condition_id TEXT, goods_id TEXT, offers_condition_sku_count INTEGER, offers_condition_value REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Offers_Conditions_Bonus (id TEXT primary key, offers_id TEXT, offers_conditions_id TEXT, goods_id TEXT, bonus_count REAL, bonus_price REAL, bonus_discount REAL, bonus_numb INTEGER,comment TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Messages (id TEXT primary key, employees_id TEXT, date_begin TEXT, date_end TEXT, message_subject TEXT, message_body TEXT,is_active INTEGER )");
		
		
		// *** service *** \\
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_OrderNotes (id TEXT primary key,descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_PaymentNotes (id TEXT primary key,descr TEXT)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_ReturnNotes (id TEXT primary key,descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_AccType (id TEXT primary key, descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xenum_ReturnBases (id TEXT primary key, descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_VisitCauses (id TEXT primary key, descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_TaskTemplates (id TEXT primary key, descr TEXT)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_PhotoReportTypes (id TEXT primary key, descr TEXT)");
		
// *** documents *** \\		

		/// orders
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, date_execute TEXT, state INTEGER, verstamp INTEGER, acctype TEXT, firms_id TEXT, departments_id TEXT, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_ordersum REAL, total_restsum REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_H_date_create On xdoc_Orders_H (date_create)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_H_customers_id On xdoc_Orders_H (customers_id)");		
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, date_execute TEXT, state INTEGER, verstamp INTEGER, acctype TEXT, firms_id TEXT, departments_id TEXT, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_ordersum REAL, total_restsum REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_T (id TEXT primary key,hdoc_id TEXT, lineno INTEGER, goods_id TEXT, goods_feature_id TEXT, goods_measures_id, factor REAL, ordervalue REAL, restvalue REAL, nextvalue REAL, price REAL, order_sum REAL,rest_sum REAL)");		
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_T_hdoc_id On xdoc_Orders_T (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_T_hdoc_id_goods_id On xdoc_Orders_T (hdoc_id,goods_id)");		
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_T (id TEXT primary key,hdoc_id TEXT, lineno INTEGER, goods_id TEXT, goods_feature_id TEXT, goods_measures_id, factor REAL, ordervalue REAL, restvalue REAL, nextvalue REAL, price REAL, order_sum REAL,rest_sum REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_D_hdoc_id On xdoc_Orders_D (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_G (id TEXT primary key,hdoc_id TEXT,tdoc_id TEXT,ordersgoodsdetails_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_G_hdoc_id On xdoc_Orders_G (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_G_tdoc_id On xdoc_Orders_G (tdoc_id)");		
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_G (id TEXT primary key,hdoc_id TEXT,tdoc_id TEXT,ordersgoodsdetails_id TEXT)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_O (id TEXT primary key, hdoc_id TEXT, tdoc_id TEXT, offers_id TEXT, offers_conditions_id TEXT, goods_id TEXT, offervalue REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_O_hdoc_id On xdoc_Orders_O (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_O (id TEXT primary key, hdoc_id TEXT, tdoc_id TEXT, offers_id TEXT, offers_conditions_id TEXT, goods_id TEXT, offervalue REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Orders_B (id TEXT primary key, hdoc_id TEXT, tdoc_id TEXT, offers_id TEXT, offers_conditions_id TEXT, offers_conditions_bonus_id TEXT, goods_id TEXT, bonus_count REAL, comment TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Orders_B_hdoc_id On xdoc_Orders_B (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Orders_B (id TEXT primary key, hdoc_id TEXT, tdoc_id TEXT, offers_id TEXT, offers_conditions_id TEXT, offers_conditions_bonus_id TEXT, goods_id TEXT, bonus_count REAL, comment TEXT)");
		
		
		/// payments
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Payments_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, state INTEGER, verstamp INTEGER, acctype TEXT, firms_id TEXT, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_paymentsum REAL)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Payments_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, state INTEGER, verstamp INTEGER, acctype TEXT, firms_id TEXT, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_paymentsum REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Payments_T (id TEXT primary key,hdoc_id TEXT,ddoc_id TEXT,dtypedoc_id TEXT,dtypedoc_descr TEXT, ddocnum TEXT, ddocdate TEXT,dorder_sum REAL,payment_sum REAL)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Payments_T_hdoc_id_ddoc_id On xdoc_Payments_T (hdoc_id,ddoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Payments_T (id TEXT primary key,hdoc_id TEXT,ddoc_id TEXT,dtypedoc_id TEXT,dtypedoc_descr TEXT, ddocnum TEXT, ddocdate TEXT,dorder_sum REAL,payment_sum REAL)");
		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Payments_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");				
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Payments_D_hdoc_id On xdoc_Payments_D (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Payments_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");
		
		
		
		/// returns
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Returns_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, date_execute TEXT, state INTEGER, verstamp INTEGER, acctype TEXT, firms_id TEXT, departments_id TEXT, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_returnsum REAL, return_type INTEGER)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Returns_T (id TEXT primary key, hdoc_id TEXT, lineno INTEGER, goods_id TEXT, goods_feature_id TEXT, goods_measures_id, factor REAL, return_value REAL, create_date TEXT, return_base TEXT, price REAL, return_sum REAL, date_type INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Returns_T_hdoc_id On xdoc_returns_T (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Returns_T_hdoc_id_goods_id On xdoc_returns_T (hdoc_id,goods_id)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Returns_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Returns_D_hdoc_id On xdoc_Returns_D (hdoc_id)");

		/// photos
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_PhotoReports_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, state INTEGER, verstamp INTEGER, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER, total_photo REAL)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_PhotoReports_T (id TEXT primary key, hdoc_id TEXT, docline INTEGER, photoreportype_id TEXT, sended INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_PhotoReports_T_hdoc_id On xdoc_PhotoReports_T (hdoc_id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_PhotoReports_D (id TEXT primary key,hdoc_id TEXT,comment_id TEXT,descr TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_PhotoReports_D_hdoc_id On xdoc_PhotoReports_D (hdoc_id)");
		
		/// gpslog
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_GpsLog (id TEXT primary key, type_xdoc INTEGER, date_create TEXT, state INTEGER, user_id TEXT, longtitude REAL,lattitude REAL,accuracy REAL,locationTime TEXT)");				
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_GpsLog_id On xdoc_GpsLog (id)");		
		
		/// blanks
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Blanks_H (id TEXT primary key, docnum numeric, date_create TEXT, date_save TEXT, state INTEGER, verstamp INTEGER, user_id TEXT, employees_id TEXT, customers_id TEXT, planned INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Blanks_H_date_create On xdoc_Blanks_H (date_create)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Blanks_T (id TEXT primary key,hdoc_id TEXT, blank_id TEXT, blankstructure_id TEXT, blank_value TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Blanks_T_hdoc_id On xdoc_Blanks_T (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Blanks_T_blank_id_blankstructure_id On xdoc_Blanks_T (blank_id,blankstructure_id)");		
				
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_ReadedMessages (id TEXT primary key, message_id TEXT, state INTEGER, employees_id TEXT, date_read TEXT )");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_ReadedMessages_message_id On xdoc_ReadedMessages (message_id)");		
		
		/// visits
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Visits_H (id TEXT primary key, docnum NUMERIC, date_create TEXT, date_save TEXT, date_planned TEXT ,state INTEGER, verstamp INTEGER, user_id TEXT, employees_id TEXT, customers_id TEXT, visit_state INTEGER, VisitCauses_id TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Visits_H_docnum On xdoc_Visits_H (docnum)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Visits_H_customers_id On xdoc_Visits_H (customers_id)");
		
		/// tasks
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Tasks_H (id TEXT primary key, docnum NUMERIC, date_create TEXT, date_save TEXT, date_target TEXT, state INTEGER, verstamp INTEGER, user_id TEXT, employees_id TEXT, customers_id TEXT, task_descr TEXT, task_state INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Tasks_H_date_target On xdoc_Tasks_H (date_target)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Tasks_H_date_create On xdoc_Tasks_H (date_create)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Tasks_H (id TEXT primary key, docnum NUMERIC, date_create TEXT, date_save TEXT, date_target TEXT, state INTEGER, verstamp INTEGER, user_id TEXT, employees_id TEXT, customers_id TEXT, task_descr TEXT, task_state INTEGER)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xdoc_Tasks_T (id TEXT primary key, hdoc_id TEXT, task_id TEXT, task_descr TEXT, task_state INTEGER)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xdoc_Tasks_T_hdoc_id On xdoc_Tasks_T (hdoc_id)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE sxdoc_Tasks_T (id TEXT primary key, hdoc_id TEXT, task_id TEXT, task_descr TEXT, task_state INTEGER)");
		
		// reports
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_Reports (id TEXT primary key,descr TEXT, descr_full TEXT, reportfile TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE TABLE xref_ReportsFields (id TEXT primary key, reports_id TEXT, reports_field TEXT)");		
		MainGlobalModule.DBE .execSQL("CREATE INDEX xref_ReportsFields_reports_id on xref_ReportsFields (reports_id)");
		
		// systems
		MainGlobalModule.DBE .execSQL("CREATE TABLE xsys_MobileConstants (id TEXT primary key, name TEXT,descr TEXT, value TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xsys_MobileConstants_name On xsys_MobileConstants (name)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xsys_settings (id TEXT primary key, value TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xsys_settings_id On xsys_settings (id)");
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xsys_errors (id TEXT primary key, date_save TEXT, state INTEGER, error_content TEXT,addvalue_0 TEXT,addvalue_1 TEXT,addvalue_2 TEXT)");		
		
		MainGlobalModule.DBE .execSQL("CREATE TABLE xstruct_tree (id TEXT primary key, it TEXT)");
		MainGlobalModule.DBE .execSQL("CREATE INDEX xstruct_tree_it On xstruct_tree (it)");
		
	}
	
	public void InitializeDataBase () {
		
		// initialize storage folder and database
		File sdCard = Environment.getExternalStorageDirectory();		
		MainGlobalModule.StoragePath = sdCard.getAbsolutePath ()+"/" + "xenia";
		File directory = new File(MainGlobalModule.StoragePath);
	    File dbFile = new File(directory, "xenia.db");	    
	    if (!directory.exists())
	    {
	    	directory.mkdirs();	
	    }

	    MainGlobalModule.DBE = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
	    try {
	    	create_tables ();	
		} catch (Exception e) {
			e.printStackTrace();
		}
	    
	    
	    // initialize goods images folder	    
	    MainGlobalModule.PhotoReportsImagesPath = MainGlobalModule.StoragePath+"/photoreports";
	    File photoreports_directory = new File(MainGlobalModule.PhotoReportsImagesPath);
	    if (!photoreports_directory.exists())
	    {
	    	photoreports_directory.mkdirs();	
	    }
	    
	    // initialize goods images folder	    
	    MainGlobalModule.GoodsImagesPath = MainGlobalModule.StoragePath+"/goodsimages";
	    File goods_images_directory = new File(MainGlobalModule.GoodsImagesPath);
	    if (!goods_images_directory.exists())
	    {
	    	goods_images_directory.mkdirs();	
	    }
	    
	    
	    // initialize goods images thumbs folder
	    MainGlobalModule.GoodsImagesThumbsPath = MainGlobalModule.GoodsImagesPath+"/thumbs";
	    File thumbs_goods_images_directory = new File(MainGlobalModule.GoodsImagesThumbsPath);
	    if (!thumbs_goods_images_directory.exists())
	    {
	    	thumbs_goods_images_directory.mkdirs();	
	    }
	    
	    // initialize updates folder
	    MainGlobalModule.UpdatePath = MainGlobalModule.StoragePath+"/update";
	    File updates_directory = new File(MainGlobalModule.UpdatePath);
	    if (!updates_directory.exists()) 
	    {
	    	updates_directory.mkdirs();	
	    }

	    // initialize and clear temporary folder
	    MainGlobalModule.TemporaryPath = MainGlobalModule.StoragePath + "/Temp"; 
	    File temporary_directory = new File(MainGlobalModule.TemporaryPath);
	    if (!temporary_directory.exists())
	    {
	    	temporary_directory.mkdirs();	
	    };
	    File [] tFiles = temporary_directory.listFiles();
	    if (tFiles!=null) {
	    	for (int i = 0; i < tFiles.length; i++) {
				File file = tFiles[i];
				file.delete();
			}
	    }
	    
	   
	    
	    
	}
	
}
